package database.magazina;

import database.common.ConnectionFactory;
import model.magazina.Furnizim;
import model.magazina.FurnizimDetail;
import model.magazina.Transferim;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * Author: Kostika Gorica
 * User:   kosta
 * Date:   1/3/13
 * Time:   8:10 PM
 */
public class TransferimDao {
    private static final String TRANSFERIM_INSERT =
            "INSERT INTO magazina.transferim (artikull_id, operator_id, magazina_burim, magazina_dest, data_transferimit, sasia ) VALUES (?,?,?,?,?,?)";
    private static final String TRANSFERIM_UPDATE =
            "UPDATE magazina.transferim SET artikull_id=?, operator_id=?, magazina_burim=?, magazina_dest=?, data_transferimit=?, sasia=? WHERE id  = ?";
    private static final String SELECT_SINGLE_TRANSFERIM =
            "SELECT id, artikull_id, operator_id, magazina_burim, magazina_dest, data_transferimit, sasia FROM magazina.transferim WHERE id = ? ";
    private static final String SELECT_TRANSFERIME_BY_DATE_XML =
            "SELECT query_to_xml" +
            "('SELECT * from magazina.transferim WHERE date(data_transferimit)>=date('||?||') AND date(data_transferimit)<=date('||?||') ORDER BY data_transferimit '," +
            "true,false,'')";
    private static final String SELECT_TRANSFERIME_BY_BURIM_XML =
            "SELECT query_to_xml" +
            "('SELECT * from magazina.transferim WHERE magazina_burim='||?|' AND date(data_transferimit)>=date('||?||') AND date(data_transferimit)<=date('||?||') ORDER BY data_transferimit '," +
            "true,false,'')";
    private static final String SELECT_TRANSFERIME_BY_DEST_XML =
            "SELECT query_to_xml" +
            "('SELECT * from magazina.transferim WHERE magazina_dest='||?|' AND date(data_transferimit)>=date('||?||') AND date(data_transferimit)<=date('||?||') ORDER BY data_transferimit '," +
            "true,false,'')";

    private static int insert(Transferim transferim){
        PreparedStatement statement = null;
        Connection connection = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(TRANSFERIM_INSERT);
            statement.setInt(1, transferim.getArtikulliID());
            statement.setInt(2, transferim.getOperatoriID());
            statement.setInt(3, transferim.getMagazinaBurimID());
            statement.setInt(4, transferim.getMagazinaDestinacionID());
            statement.setTimestamp(5, new Timestamp(transferim.getDataTransferimit().getTime()));
            statement.setDouble(6, transferim.getSasia());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static int update(Transferim transferim){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(TRANSFERIM_UPDATE);
            if (transferim.getId()<1){
                return -1;
            }
            statement.setInt(1, transferim.getArtikulliID());
            statement.setInt(2, transferim.getOperatoriID());
            statement.setInt(3, transferim.getMagazinaBurimID());
            statement.setInt(4, transferim.getMagazinaDestinacionID());
            statement.setTimestamp(5, new Timestamp(transferim.getDataTransferimit().getTime()));
            statement.setDouble(6, transferim.getSasia());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static Transferim selectSingleTransferim(int id){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Transferim transferim = null;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_SINGLE_TRANSFERIM);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                transferim.setId(resultSet.getInt(1));
                transferim.setArtikulliID(resultSet.getInt(2));
                transferim.setOperatoriID(resultSet.getInt(3));
                transferim.setMagazinaBurimID(resultSet.getInt(4));
                transferim.setMagazinaDestinacionID(resultSet.getInt(5));
                transferim.setDataTransferimit(resultSet.getTimestamp(6));
                transferim.setSasia(resultSet.getDouble(7));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (resultSet!=null) resultSet.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return transferim;
    }


    public static String selectByBurimi(int magazina, java.util.Date startDate,java.util.Date endDate){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String transferime = "";
        try {
            connection = ConnectionFactory.getConnection();
            //TODO if magazina has childs
            statement = connection.prepareStatement(SELECT_TRANSFERIME_BY_BURIM_XML);
            statement.setInt(1, magazina);
            statement.setDate(2, new Date(startDate.getTime()));
            statement.setDate(3, new Date(endDate.getTime()));
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                transferime = resultSet.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (resultSet!=null) resultSet.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return transferime;
    }

    public static String selectByDestinacioni(int magazina, java.util.Date startDate,java.util.Date endDate){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String transferime = "";
        try {
            connection = ConnectionFactory.getConnection();
            //TODO if magazina has childs
            statement = connection.prepareStatement(SELECT_TRANSFERIME_BY_DEST_XML);
            statement.setInt(1, magazina);
            statement.setDate(2, new Date(startDate.getTime()));
            statement.setDate(3, new Date(endDate.getTime()));
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                transferime = resultSet.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (resultSet!=null) resultSet.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return transferime;
    }

    public static String selectByDate(java.util.Date startDate,java.util.Date endDate){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String transferime = "";
        try {
            connection = ConnectionFactory.getConnection();
            //TODO if magazina has childs
            statement = connection.prepareStatement(SELECT_TRANSFERIME_BY_BURIM_XML);
            statement.setDate(1, new Date(startDate.getTime()));
            statement.setDate(2, new Date(endDate.getTime()));
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                transferime = resultSet.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (resultSet!=null) resultSet.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return transferime;
    }
}
